package com.solo.dao;

import com.solo.entity.Category;
import com.solo.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class CategoryDAO {

    Connection conn = null;
    PreparedStatement pst = null;
    ResultSet rs = null;

    /**
     * 新增分类
     * @param category
     * @return
     */
    public boolean addCategory(Category category) {
        boolean state = false;
        conn = DBUtil.getConn();
        try {
            // String sql = "insert into `sys_user`(username,password,name,email,phonenumber,gender) values(?, ?, ?, ?, ?, ?)";
            String sql = "insert into `blog_category`(name,order_num) values(?, ?)";
            pst = conn.prepareStatement(sql);
            pst.setString(1, category.getName());
            pst.setString(2, category.getOrderNum());

            int index = pst.executeUpdate();
            if (index > 0) {
                state = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }
        return state;
    }

    /**
     * 查询所有分类
     *
     * @return
     * @throws Exception
     */
    public List<Category> selectAllCategory() {
        conn = DBUtil.getConn();
        List<Category> listCategory = new ArrayList<>();
        try {
            String sql = "SELECT * FROM blog_category";
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            // 也可以使用createStatement()
            // Statement statement = conn.createStatement();
            // ResultSet resultSet = statement.executeQuery(sql);

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String orderNum = rs.getString("order_num");
                // 调用构造方法实例化User对象
                Category Category = new Category(id, name, orderNum);
                listCategory.add(Category);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }
        return listCategory;
    }

    /**
     * 删除用户
     * @param category
     * @return
     */
    public boolean deleteCategory(Category category) {
        boolean rowDeleted = false;
        conn = DBUtil.getConn();
        try {
            String sql = "DELETE FROM blog_category where id = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setLong(1, category.getId());

            rowDeleted = statement.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }
        return rowDeleted;
    }

    /**
     * 更新用户信息
     * @param category
     * @return
     */
    public boolean updateCategory(Category category) {
        boolean rowUpdated = false;
        conn = DBUtil.getConn();

        try {
            String sql = "UPDATE blog_category SET name = ?, order_num = ?";
            sql += " WHERE id = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, category.getName());
            statement.setString(2, category.getOrderNum());
            statement.setLong(3, category.getId());

            rowUpdated = statement.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }
        return rowUpdated;
    }

    /**
     * 按id号查询用户
     * @param id
     * @return
     */
    public Category selectCategoryById(Long id) {
        conn = DBUtil.getConn();
        Category Category = null;
        try {
            String sql = "SELECT * FROM blog_category WHERE id = ?";
            pst = conn.prepareStatement(sql);
            pst.setLong(1, id);
            rs = pst.executeQuery();
            if (rs.next()) {
                String name = rs.getString("name");
                String orderNum = rs.getString("order_num");
                Category = new Category(id, name,orderNum);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(rs, pst, conn);
        }

        return Category;
    }
}
